DROP TABLE IF EXISTS testtbl
CREATE TABLE testtbl 
    (id      int  NOT NULL,
     datecol date NOT NULL,
     CONSTRAINT pk_testtbl PRIMARY KEY (id))
go
CREATE OR ALTER PROCEDURE error_handler_sp AS
SET XACT_ABORT, NOCOUNT ON
 
   DECLARE @errno    int            = error_number(),
           @severity int            = error_severity(),
           @state    int            = error_state(),
           @proc     sysname        = error_procedure(),
           @lineno   int            = error_line(),
           @errmsg   nvarchar(2048) = error_message()

   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + 
                       '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)
   -- SELECT @severity += 50000--, @errmsg = replace(@errmsg, '%', '%%')
   -- ; THROW @severity, @errmsg, @state
go
CREATE OR ALTER PROCEDURE inner_sp AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   PRINT '---inner_sp starting'

   --BACKUP DATABASE master TO DISK = 'K:\nosuchdisk.bak'

   BEGIN TRANSACTION

   INSERT testtbl(id, datecol) VALUES (11, '2001-01-01')

   INSERT testtbl(id, datecol)
      VALUES (12, '2002-02-02'), 
             ('13%', NULL), 
             (14, '2004-04-04')

   INSERT testtbl(id, datecol) VALUES (15, '2005-05-05')

   COMMIT TRANSACTION
   PRINT '---inner_sp completed'
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
END CATCH
go
CREATE OR ALTER PROCEDURE outer_sp AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   PRINT 'outer_sp starting'
   EXEC inner_sp
   PRINT 'outer_sp completed'
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
END CATCH
go
EXEC inner_sp
--EXEC outer_sp
go
SELECT id, datecol FROM testtbl ORDER BY id
IF @@trancount > 0 
BEGIN
   PRINT 'AFTER THE TEST THERE IS AN ACTIVE TRANSACTION!'
   ROLLBACK TRANSACTION
END
ELSE 
   PRINT 'No transaction is active after the test'
